package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.basedoc.model.Room;
import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.core.util.PropertyUtils;
import com.eling.elcms.ordermanager.model.ServicePoint;
import com.eling.elcms.travel.dao.IRoomDao;
import com.eling.elcms.travel.model.CheckIn;
import com.eling.elcms.travel.model.view.RoomdStatisticsView;
import org.springframework.stereotype.Repository;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class RoomdStatisticsDaoHibernate extends GenericDaoHibernate<Room, Long> implements IRoomDao {

	public RoomdStatisticsDaoHibernate() {
		super(Room.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RoomdStatisticsView> queryT(CheckIn cond) {
		Object property2 = PropertyUtils.getProperty(cond,"checkInDate");//当天
//		Object property3 = PropertyUtils.getProperty(cond,"checkOutDate");//明天
		ServicePoint property = PropertyUtils.getProperty(cond,"servicePoint");
		int psp = 1;
		if (property != null) {
			Long pkServicePoint = property.getPkServicePoint();
			psp = pkServicePoint.intValue();
		}
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
//		DateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateC = df.format((Date)property2);
//		String dateCD = df.format((Date)property3);
		
		
		List<RoomdStatisticsView> list = new ArrayList<RoomdStatisticsView>();
		RoomdStatisticsView rsv = new RoomdStatisticsView();
		
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append("trr.name '房型',");
		sb.append("count(br.pkRoom) '总房数',  ");
		sb.append("SUM(CASE WHEN br.status = 'Repairing' THEN 1 ELSE 0 END) AS  'xRoom维修房',  ");
		sb.append("SUM(CASE WHEN br.status = 'Clear' THEN 1 ELSE 0 END) AS  'kRoom可售房'  ");
		sb.append("FROM bd_room br ");
		sb.append("LEFT JOIN travel_roomtype trr ON br.pkRoomType = trr.pkRoomType ");
		
		sb.append("LEFT JOIN bd_building bb ON br.pkOrgBuilding = bb.pkOrgBuilding  ");
		sb.append("LEFT JOIN om_servicepoint om ON bb.pkServicePoint = om.pkServicePoint  ");//加上服务点 区分机构号
		sb.append("LEFT JOIN cm_organization co ON om.pkOrganization = co.pkOrganization  ");
		
		sb.append("WHERE  ");
		
		sb.append("om.pkServicePoint =:pkpkServicePoint  ");
		
		sb.append("GROUP BY trr.pkRoomType");
		String sql = sb.toString();
			
		List<Object[]> l1 = getSession().createNativeQuery(sql).setParameter("pkpkServicePoint",psp).list();
		for(Object[] o : l1){
			rsv = new RoomdStatisticsView();
			rsv.setRoomX(o[0].toString());
			rsv.setSumRoom(Integer.parseInt(o[1].toString()));
			rsv.setxRoom(Integer.parseInt(o[2].toString()));
			rsv.setkRoom(Integer.parseInt(o[3].toString()));
			list.add(rsv);
		}
		
		/*      散客住房       */
		StringBuffer sb1 = new StringBuffer();
		sb1.append("SELECT ");
		sb1.append("trr.name as roomX,count(br.pkRoom) as sRoom ");
		sb1.append("FROM bd_room br ");
		sb1.append("LEFT JOIN travel_roomtype trr ON br.pkRoomType = trr.pkRoomType ");
		sb1.append("LEFT JOIN travel_roomregister trg ON trg.pkRoomRegister = br.pkRoom ");
		sb1.append("LEFT JOIN travel_roompersonalInfo trp ON trp.pkRoomRegister = trg.pkRoomRegister ");
		sb1.append("LEFT JOIN travel_checkinimplement tcii ON trp.pkCIImplement = tcii.pkCIImplement ");
		sb1.append("LEFT JOIN travel_nonmemgroupreserve tnmgr ON tcii.pkNonMemGroupReserve = tnmgr.pkNonMemGroupReserve ");
		sb1.append("LEFT JOIN travel_nonmembergroup tmg ON tmg.pkNonMemberGroup = tnmgr.pkNonMemberGroup ");
		sb1.append("LEFT JOIN travel_checkin ck ON trg.pkRoomRegister = ck.pkRoomRegister ");
		
		sb1.append("LEFT JOIN bd_building bb ON br.pkOrgBuilding = bb.pkOrgBuilding  ");
		sb1.append("LEFT JOIN om_servicepoint om ON bb.pkServicePoint = om.pkServicePoint  ");//加上服务点 区分机构号
		sb1.append("LEFT JOIN cm_organization co ON om.pkOrganization = co.pkOrganization  ");
		
		sb1.append("WHERE  ");//
		sb1.append("om.pkServicePoint =:pkpkServicePoint  ");
		sb1.append("AND tmg.`status` = 'CheckIn'   "); //已经入住
		// 入住日期<=今天的 23:59:59"  退房日期>=今天 的都算是在住的。
		sb1.append("AND ck.checkInDate <= '"+dateC+" 23:59:59"+"'   ");
		sb1.append("AND ck.checkOutDate >= '"+dateC+"'  ");
		sb1.append("GROUP BY trr.pkRoomType");
		List<Object[]> l2 = getSession().createNativeQuery(sb1.toString()).setParameter("pkpkServicePoint", psp).list();
		for(Object[] o : l2){
			for(int i = 0;i<list.size();i++){
				if(o[0].toString().equals(list.get(i).getRoomX())){
					rsv = list.get(i);
					rsv.setsRoom(Integer.parseInt(o[1].toString()));
					list = lrplace(list, i, rsv);
					break;
				}
			}
		}
		
		/*      散客预订       */
		sb1 = new StringBuffer();
		sb1.append("SELECT ");
		sb1.append("tr.`name` '房型',count(br.pkRoom) '房数' ");
		sb1.append("FROM bd_room br ");
		sb1.append("LEFT JOIN travel_roomtype tr ON br.pkRoomType = tr.pkRoomType ");
		sb1.append("LEFT JOIN travel_signuproom ts ON tr.pkRoomType = ts.pkRoomType ");
		sb1.append("LEFT JOIN travel_nonmemgroupreserve tn2 ON ts.pkNonMemGroupReserve = tn2.pkNonMemGroupReserve ");
		sb1.append("LEFT JOIN travel_nonmembergroup tn ON tn2.pkNonMemberGroup = tn.pkNonMemberGroup ");
		sb1.append("LEFT JOIN travel_nonmemberregister tn1 ON tn.pkNonMemberGroup = tn1.pkNonMemberGroup ");
		
		sb1.append("LEFT JOIN bd_building bb ON br.pkOrgBuilding = bb.pkOrgBuilding  ");
		sb1.append("LEFT JOIN om_servicepoint om ON bb.pkServicePoint = om.pkServicePoint  ");//加上服务点 区分机构号
		sb1.append("LEFT JOIN cm_organization co ON om.pkOrganization = co.pkOrganization  ");
		
		sb1.append("WHERE   ");
		sb1.append("tn.sourceType = 'Reservation'   ");//预约
		sb1.append("AND ts.roomStatus = 'Pending'   ");
		sb1.append("AND tn.checkInDate <= '"+dateC+" 23:59:59"+"'   ");
		sb1.append("AND tn.checkOutDate >= '"+dateC+"'   ");
		sb1.append("AND tn.signUpType = 'Experience' ");//体检才有预约的房型，散客在预约的时候没保存房型
		sb1.append("AND om.pkServicePoint =:pkpkServicePoint  ");
		sb1.append("GROUP BY tr.pkRoomType");
		l2 = getSession().createNativeQuery(sb1.toString()).setParameter("pkpkServicePoint", psp).list();

		for(Object[] o : l2){
			for(int i = 0;i<list.size();i++){
				if(o[0].toString().equals(list.get(i).getRoomX())){
					rsv = list.get(i);
					rsv.setSyRoom(Integer.parseInt(o[1].toString()));
					list = lrplace(list, i, rsv);
					break;
				}
			}
		}
		
		/*       团体住房       */
		StringBuffer sb2 = new StringBuffer();
		sb2.append("SELECT ");
		sb2.append("rt.`name` '房型',count(ro.pkRoom) '团体住房数量'  ");
		sb2.append("FROM bd_room ro  ");
		sb2.append("LEFT JOIN travel_roomtype rt ON ro.pkRoomType = rt.pkRoomType ");
		sb2.append("LEFT JOIN travel_roomregister r ON ro.pkRoom = r.pkRoom ");
		sb2.append("LEFT JOIN travel_group_roomregister room ON r.pkRoomRegister = room.pkRoomRegister ");
		sb2.append("LEFT JOIN travel_groupreserve pi ON room.pkGroupReserve = pi.pkGroupReserve ");
		sb2.append("LEFT JOIN	travel_checkinimplement cii ON pi.pkGroupReserve = cii.pkGroupReserve ");
		sb2.append("LEFT JOIN travel_planitemgroup pgroup ON pi.pkPlanItemGroup = pgroup.pkPlanItemGroup  ");
		sb2.append("LEFT JOIN travel_planitem pitem ON pgroup.pkPlanItem = pitem.pkPlanItem  ");
		sb2.append("LEFT JOIN travel_destination dest ON pitem.pkDestination = dest.pkDestination  ");
		sb2.append("LEFT JOIN travel_roompersonalInfo rp ON cii.pkCIImplement = rp.pkCIImplement  ");
		sb2.append("LEFT JOIN bd_building b ON ro.pkOrgBuilding = b.pkOrgBuilding  ");
		sb2.append("LEFT JOIN travel_checkin ck ON r.pkRoomRegister = ck.pkRoomRegister  ");
		sb2.append("LEFT JOIN sm_user u ON ck.operator = u.pkUser  ");
		
		sb2.append("LEFT JOIN om_servicepoint om ON b.pkServicePoint = om.pkServicePoint  ");//加上服务点 区分机构号
		sb2.append("LEFT JOIN cm_organization co ON om.pkOrganization = co.pkOrganization  ");
		
		sb2.append("WHERE  ");
		sb2.append("om.pkServicePoint =:pkServicePoint  ");
		sb2.append("AND ck.checkInDate <= '"+dateC+" 23:59:59"+"'        ");
		sb2.append("AND ck.checkOutDate >= '"+dateC+"'        ");
//		sb2.append("AND pi.checkInType = 'Organization'        ");
		sb2.append("GROUP BY rt.pkRoomType ");
		l2 = getSession().createNativeQuery(sb2.toString()).setParameter("pkServicePoint", psp).list();
		
		for(Object[] o : l2){
			for(int i = 0;i<list.size();i++){
				if(o[0].toString().equals(list.get(i).getRoomX())){
					rsv = list.get(i);
					rsv.settRoom(Integer.parseInt(o[1].toString()));
					list = lrplace(list, i, rsv);
					break;
				}
			}
		}
		
		/*       团体预订 (tp.alliance = '0' 联盟有专门的住房)      */
		sb2 = new StringBuffer();
		sb2.append("SELECT ");
		sb2.append("tr2.`name` '房型',count(br.`code`) '房数'  ");
		sb2.append("FROM bd_room br ");
		sb2.append("LEFT JOIN travel_roomtype tr2 ON br.pkRoomType = tr2.pkRoomType ");
		sb2.append("LEFT JOIN travel_roomregister tr ON br.pkRoom = tr.pkRoom ");
		sb2.append("LEFT JOIN travel_group_roomregister tgr ON tr.pkRoomRegister = tgr.pkRoomRegister ");
		sb2.append("LEFT JOIN travel_groupreserve tg ON tgr.pkGroupReserve = tg.pkGroupReserve ");
		sb2.append("LEFT JOIN travel_planitemgroup tp ON tg.pkPlanItemGroup = tp.pkPlanItemGroup ");
		sb2.append("LEFT JOIN travel_planitem tp1 ON tp.pkPlanItem = tp1.pkPlanItem   ");
		
		sb2.append("LEFT JOIN bd_building bb ON br.pkOrgBuilding = bb.pkOrgBuilding  ");
		sb2.append("LEFT JOIN om_servicepoint om ON bb.pkServicePoint = om.pkServicePoint  ");//加上服务点 区分机构号
		sb2.append("LEFT JOIN cm_organization co ON om.pkOrganization = co.pkOrganization  ");
		
		sb2.append("WHERE ");
		sb2.append("tp.alliance = '0' ");//非联盟
		sb2.append("AND om.pkServicePoint =:pkServicePoint  ");
		sb2.append("AND (tp.checkInStatus = 'Initial'	OR tp.checkInStatus = 'Reserve')   ");
		sb2.append("AND tp1.startDate >= '"+dateC+" 23:59:59"+"'  ");
		sb2.append("AND tp1.endDate <= '"+dateC+"'   ");
		sb2.append("GROUP BY 	tr2.pkRoomType  ");
		l2 = getSession().createNativeQuery(sb2.toString()).setParameter("pkServicePoint", psp).list();
		
		for(Object[] o : l2){
			for(int i = 0;i<list.size();i++){
				if(o[0].toString().equals(list.get(i).getRoomX())){
					rsv = list.get(i);
					rsv.setTyRoom(Integer.parseInt(o[1].toString()));
					list = lrplace(list, i, rsv);
					break;
				}
			}
		}
		
		/* 将离房 */
		sb2 = new StringBuffer();
		sb2.append("SELECT ");
		sb2.append("	tr. NAME, ");
		sb2.append("	COUNT(br.pkRoom) AS lroom ");
		sb2.append("FROM bd_room br ");
		sb2.append("LEFT JOIN travel_roomregister trg ON trg.pkRoom = br.pkRoom ");
		sb2.append("LEFT JOIN travel_checkin tci ON tci.pkRoomRegister = trg.pkRoomRegister ");
		sb2.append("LEFT JOIN travel_roomtype tr ON tr.pkRoomType = br.pkRoomType ");
		
		sb2.append("LEFT JOIN bd_building bb ON br.pkOrgBuilding = bb.pkOrgBuilding  ");
		sb2.append("LEFT JOIN om_servicepoint om ON bb.pkServicePoint = om.pkServicePoint  ");//加上服务点 区分机构号
		sb2.append("LEFT JOIN cm_organization co ON om.pkOrganization = co.pkOrganization  ");
		
		sb2.append("WHERE 1=1  ");
		sb2.append("AND om.pkServicePoint=:pkServicePoint ");
		sb2.append("AND tci.checkOutDate LIKE '"+dateC+"%' ");
		sb2.append("GROUP BY 	br.pkRoomType ");
		
		l2 = getSession().createNativeQuery(sb2.toString()).setParameter("pkServicePoint", psp).list();
		
		for(Object[] o : l2){
			for(int i = 0;i<list.size();i++){
				if(o[0]==null){
					break;
				}
				if(o[0].toString().equals(list.get(i).getRoomX())){
					rsv = list.get(i);
					rsv.setlRoom(Integer.parseInt(o[1].toString()));
					list = lrplace(list, i, rsv);
					break;
				}
			}
		}
		
		/* 合计使用房  */
		for(int i = 0;i<list.size();i++){
			rsv = list.get(i);
			if(rsv.getsRoom()==null){
				rsv.setsRoom(0);
			}
			if(rsv.gettRoom()==null){
				rsv.settRoom(0);
			}
			rsv.setHyRoom(rsv.getsRoom()+rsv.gettRoom());
			list = lrplace(list, i, rsv);
		}
		
		/* 合计用房(不含将离) */
		for(int i = 0;i<list.size();i++){
			rsv = list.get(i);
			if(rsv.getHyRoom()==null){
				rsv.setHyRoom(0);
			}
			if(rsv.getlRoom()==null){
				rsv.setlRoom(0);
			}
			rsv.sethRoom(rsv.getHyRoom()-rsv.getlRoom());
			list = lrplace(list, i, rsv);
		}
		
		/* 剩余房(含维修房) */
		for(int i = 0;i<list.size();i++){
			rsv = list.get(i);
			if(rsv.getSumRoom()==null){
				rsv.setSumRoom(0);
			}
			if(rsv.getHyRoom()==0){
				rsv.setHyRoom(0);
			}
			rsv.setSyhRoom(rsv.getSumRoom()-rsv.getHyRoom());
			list = lrplace(list, i, rsv);
		}
		
		/* 剩余房(不含维修房) */
		for(int i = 0;i<list.size();i++){
			rsv = list.get(i);
			if(rsv.getSyhRoom()==null){
				rsv.setSyhRoom(0);
			}
			if(rsv.getxRoom()==null){
				rsv.setxRoom(0);
			}
			rsv.setSybRoom(rsv.getSyhRoom() - rsv.getxRoom());
			list = lrplace(list, i, rsv);
		}
		
		//查询出来的散客预订和团体预订如果是空就置为0
		for(int i = 0;i<list.size();i++){
			rsv = list.get(i);
			if(rsv.getSyRoom()==null){
				rsv.setSyRoom(0);
			}
			if(rsv.getTyRoom()==null){
				rsv.setTyRoom(0);
			}
			list = lrplace(list, i, rsv);
		}
		
		/*
		 * 合计 
		 */
		int[] cu = new int[12];
		for(int i = 0;i < list.size();i++){
			rsv = list.get(i);
			cu[0] += rsv.getSumRoom();//sumRoom
			cu[1] += rsv.getxRoom();//xRoom
			cu[2] += rsv.getkRoom();//kRoom
			cu[3] += rsv.getsRoom();//sRoom
			cu[4] += rsv.gettRoom();//tRoom
			cu[5] += rsv.getSyRoom();//syRoom
			cu[6] += rsv.getTyRoom();//tyRoom
			cu[7] += rsv.gethRoom();//hRoom
			cu[8] += rsv.getlRoom();//lRoom
			cu[9] += rsv.getHyRoom();//hyRoom
			cu[10] += rsv.getSyhRoom();//syhRoom
			cu[11] += rsv.getSybRoom();//sybRoom
		}
		rsv = new RoomdStatisticsView();
		rsv.setRoomX("合计");
		rsv.setSumRoom(cu[0]);
		rsv.setxRoom(cu[1]);
		rsv.setkRoom(cu[2]);
		rsv.setsRoom(cu[3]);
		rsv.settRoom(cu[4]);
		rsv.setSyRoom(cu[5]);
		rsv.setTyRoom(cu[6]);
		rsv.sethRoom(cu[7]);
		rsv.setlRoom(cu[8]);
		rsv.setHyRoom(cu[9]);
		rsv.setSyhRoom(cu[10]);;
		rsv.setSybRoom(cu[11]);
		list.add(rsv);
		/* **end*****/
		return list;
	}

	//将list中的b位置上的对象替换掉
	public List<RoomdStatisticsView> lrplace(List<RoomdStatisticsView> list,int b,RoomdStatisticsView value){
		list.remove(b);
		list.add(b,value);
		return list;
	}
	
}
